﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using Remind.Model;
using Remind.IDAL;
using Remind.DBUtility;

namespace Remind.AccessDAL
{
    public class Contacts : IContacts
    {
        #region sql字符串
        private const string SQL_SELECT_CONTACTS = "select cid,cname,cbirthday,csex,cface,cgn,IDcard,cBz from contacts";
        private const string SQL_SELECT_CONTACT_BY_CID = "select cid,gid,cname,cbirthday,csex,cface,cgn,IDcard,cBz from contacts where cid=@cid";
        private const string SQL_SELECT_CONTACTS_BY_GID = "select cid,cname,cbirthday,csex,cface,cgn,IDcard,cBz from contacts where gid=@gid order by cbirthday";
        private const string SQL_SELECT_CONTACTS_BY_CFL = "select gid,cid,cname,cbirthday,csex,cface,cgn,IDcard,cBz from contacts where cname like @cname";
        private const string SQL_SELECT_CONTACTS_BY_BIRTHDAY = "select cid,cname,cbirthday,csex,cface,cgn,IDcard,cBz from contacts where cbirthday=@cbirthday";
        private const string SQL_SELECT_CONTACTS_BY_CGN = "select cid,cname,cbirthday,csex,cface,cgn,IDcard,cBz from contacts where cgn=@cgn";
        private const string SQL_INSERT_CONTACTS = "insert into contacts(gid,cname,cbirthday,csex,cface,cgn,IDcard,cBz)values(@gid,@cname,@cbirthday,@csex,@cface,@cgn,@IDcard,@cBz)";

        private const string SQL_UPDATE_CONTACTS = "update contacts set gid=@gid,cname=@cname,cbirthday=@cbirthday,csex=@csex,cface=@cface,IDcard=@IDcard,cBz=@cBz where cid=@cid";
        private const string SQL_UPDATE_TEL = "UPDATE tel SET phone=@phone, phone1=@phone1, family=@family, company=@company, fax=@fax WHERE cid=@cid";
        private const string SQL_UPDATE_ADDRESS = "UPDATE address SET family=@family, company=@company, other=@other WHERE cid=@cid";
        private const string SQL_UPDATE_NETWORK = "UPDATE  network SET email=@email, qq=@qq, msn=@msn, blog=@blog, website=@website WHERE cid=@cid";
        private const string SQL_DELETE_CONTACTS = "delete from contacts where cid=@cid";

        private const string SQL_SELECT_MAX_BY_CID = "SELECT MAX(cid) from contacts";

        private const string PARM_CID = "@cid";
        private const string PARM_CNAME = "@cname";
        private const string PARM_CBIRTHDAY = "@cbirthday";
        private const string PARM_CSEX = "@csex";
        private const string PARM_CFACE = "@cface";
        private const string PARM_CGN = "@cgn";
        private const string PARM_GID = "@gid";
        private const string PARM_IDCARD = "@IDcard";
        private const string PARM_CBZ = "@cBz";
        #endregion

        #region IContacts 成员
        /// <summary>
        /// 查询所有联系人信息
        /// </summary>
        /// <returns></returns>
        public IList<ContactsInfo> GetContacts()
        {
            IList<ContactsInfo> contacts = new List<ContactsInfo>();
            using (OleDbDataReader rdr = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, SQL_SELECT_CONTACTS, null))
            {
                while (rdr.Read())
                {
                    ContactsInfo contact = new ContactsInfo();
                    contact.Cid = rdr.GetInt32(0);
                    contact.Cname = rdr.GetString(1);
                    contact.Cbirthday = rdr.GetDateTime(2);
                    contact.Csex = rdr.GetBoolean(3);
                    contact.Cface = rdr.GetInt32(4);
                    contact.Cgn = rdr.GetBoolean(5);
                    if (!rdr.IsDBNull(6))
                        contact.IdCard = rdr.GetString(6);
                    if (!rdr.IsDBNull(7))
                        contact.IdCard = rdr.GetString(7);
                    contacts.Add(contact);
                }
            }
            return contacts;
        }

        /// <summary>
        /// 根据分组号码查询对应的联系人信息
        /// </summary>
        /// <param name="gid"></param>
        /// <returns></returns>
        public IList<ContactsInfo> GetContactsByGid(int gid)
        {
            IList<ContactsInfo> contacts = new List<ContactsInfo>();
            OleDbParameter parm = new OleDbParameter(PARM_GID, OleDbType.Integer);
            parm.Value = gid;
            using (OleDbDataReader rdr = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, SQL_SELECT_CONTACTS_BY_GID, parm))
            {
                while (rdr.Read())
                {
                    ContactsInfo contact = new ContactsInfo();
                    contact.Cid = rdr.GetInt32(0);
                    contact.Cname = rdr.GetString(1);
                    contact.Cbirthday = rdr.GetDateTime(2);
                    contact.Csex = rdr.GetBoolean(3);
                    contact.Cface = rdr.GetInt32(4);
                    contact.Cgn = rdr.GetBoolean(5);
                    if (!rdr.IsDBNull(6))
                        contact.IdCard = rdr.GetString(6);
                    if (!rdr.IsDBNull(7))
                        contact.IdCard = rdr.GetString(7);
                    contacts.Add(contact);
                }
            }
            return contacts;
        }

        /// <summary>
        /// 根据联系人姓名返回联系人信息
        /// </summary>
        /// <param name="cname"></param>
        /// <returns></returns>
        public IList<ContactsInfo> GetContactsByCname(string cname)
        {
            IList<ContactsInfo> contacts = new List<ContactsInfo>();
            OleDbParameter parm = new OleDbParameter(PARM_CNAME, OleDbType.VarChar, 16);
            parm.Value = cname;
            using (OleDbDataReader rdr = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, SQL_SELECT_CONTACTS_BY_CFL, parm))
            {
                ContactsInfo contact = null;
                while (rdr.Read())
                {
                    contact = new ContactsInfo();
                    contact.Gid = rdr.GetInt32(0);
                    contact.Cid = rdr.GetInt32(1);
                    contact.Cname = rdr.GetString(2);
                    contact.Cbirthday = rdr.GetDateTime(3);
                    contact.Csex = rdr.GetBoolean(4);
                    contact.Cface = rdr.GetInt32(5);
                    contact.Cgn = rdr.GetBoolean(6);
                    if (!rdr.IsDBNull(7))
                        contact.IdCard = rdr.GetString(7);
                    if (!rdr.IsDBNull(8))
                        contact.IdCard = rdr.GetString(8);
                    contacts.Add(contact);
                    contact = null;
                }
            }
            return contacts;
        }

        /// <summary>
        /// 根据生日返回信息
        /// </summary>
        /// <param name="birthday"></param>
        /// <returns></returns>
        public IList<ContactsInfo> GetContactsByBirthday(DateTime birthday)
        {
            IList<ContactsInfo> contacts = new List<ContactsInfo>();
            OleDbParameter parm = new OleDbParameter(PARM_CBIRTHDAY, OleDbType.DBDate);
            parm.Value = birthday;
            using (OleDbDataReader rdr = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, SQL_SELECT_CONTACTS_BY_GID, parm))
            {
                while (rdr.Read())
                {
                    ContactsInfo contact = new ContactsInfo();
                    contact.Cid = rdr.GetInt32(0);
                    contact.Cname = rdr.GetString(1);
                    contact.Cbirthday = rdr.GetDateTime(2);
                    contact.Csex = rdr.GetBoolean(3);
                    contact.Cgn = rdr.GetBoolean(4);
                    contacts.Add(contact);
                }
            }
            return contacts;
        }

        /// <summary>
        /// 根据联系人编码查询联系人信息
        /// </summary>
        /// <param name="cid"></param>
        /// <returns></returns>
        public ContactsInfo GetContactByCid(int cid)
        {
            OleDbParameter parm = new OleDbParameter(PARM_CID, OleDbType.Integer);
            parm.Value = cid;
            using (OleDbDataReader rdr = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, SQL_SELECT_CONTACT_BY_CID, parm))
            {
                ContactsInfo contact = null;
                if (rdr.Read())
                {
                    contact = new ContactsInfo();
                    contact.Cid = rdr.GetInt32(0);
                    contact.Gid = rdr.GetInt32(1);
                    contact.Cname = rdr.GetString(2);
                    contact.Cbirthday = rdr.GetDateTime(3);
                    contact.Csex = rdr.GetBoolean(4);
                    contact.Cface = rdr.GetInt32(5);
                    contact.Cgn = rdr.GetBoolean(6);
                    if (!rdr.IsDBNull(7))
                        contact.IdCard = rdr.GetString(7);
                    if (!rdr.IsDBNull(8))
                        contact.CBz = rdr.GetString(8);
                }
                return contact;
            }
        }

        /// <summary>
        /// 编辑用户信息
        /// </summary>
        /// <param name="contacts"></param>
        /// <param name="idu"></param>
        /// <returns></returns>
        public int EditContacts(ContactsInfo contacts, Idu idu)
        {
            int val = 0;
            switch (idu)
            {
                case Idu.insert:
                    OleDbParameter[] parmAdd = new OleDbParameter[]{
                        new OleDbParameter(PARM_GID,OleDbType.VarChar,16),
                        new OleDbParameter(PARM_CNAME,OleDbType.VarChar,16),
                        new OleDbParameter(PARM_CBIRTHDAY,OleDbType.Date),
                        new OleDbParameter(PARM_CSEX,OleDbType.Boolean),
                        new OleDbParameter(PARM_CFACE,OleDbType.Integer),
                        new OleDbParameter(PARM_CGN,OleDbType.Boolean),
                        new OleDbParameter(PARM_IDCARD,OleDbType.VarChar,32),
                        new OleDbParameter(PARM_CBZ,OleDbType.VarChar,512)
                    };
                    parmAdd[0].Value = contacts.Gid;
                    parmAdd[1].Value = contacts.Cname;
                    parmAdd[2].Value = contacts.Cbirthday;
                    parmAdd[3].Value = contacts.Csex;
                    parmAdd[4].Value = contacts.Cface;
                    parmAdd[5].Value = true;
                    parmAdd[6].Value = contacts.IdCard;
                    parmAdd[7].Value = contacts.CBz;
                    val = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, SQL_INSERT_CONTACTS, parmAdd);
                    break;
                case Idu.update:
                    OleDbParameter[] parms = new OleDbParameter[]{
                        new OleDbParameter(PARM_GID,OleDbType.Integer),
                        new OleDbParameter(PARM_CNAME,OleDbType.VarChar,16),
                        new OleDbParameter(PARM_CBIRTHDAY,OleDbType.Date),
                        new OleDbParameter(PARM_CSEX,OleDbType.Boolean),
                        new OleDbParameter(PARM_CFACE,OleDbType.Integer),
                        new OleDbParameter(PARM_CGN,OleDbType.Boolean),
                        new OleDbParameter(PARM_CBZ,OleDbType.VarChar,512)
                    };
                    parms[0].Value = contacts.Gid;
                    parms[1].Value = contacts.Cname;
                    parms[2].Value = contacts.Cbirthday;
                    parms[3].Value = contacts.Csex;
                    parms[4].Value = contacts.Cface;
                    parms[5].Value = contacts.Cgn;
                    parms[6].Value = contacts.CBz;
                    val = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, SQL_UPDATE_CONTACTS, parms);
                    break;
                case Idu.delete:
                    OleDbParameter parm = new OleDbParameter(PARM_CID, OleDbType.Integer);
                    parm.Value = contacts.Cid;
                    val = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, SQL_DELETE_CONTACTS, parm);
                    break;
            }
            return val;
        }
        //编辑用户信息
        public int EditContacts(ContactsInfo contacts, TelInfo tel, AddressInfo addr, NetworkInfo network)
        {
            OleDbParameter[] contactsParms = { 
                                                new OleDbParameter(PARM_GID,OleDbType.Integer),
                                                new OleDbParameter(PARM_CNAME,OleDbType.VarChar,16),
                                                new OleDbParameter(PARM_CBIRTHDAY,OleDbType.Date),
                                                new OleDbParameter(PARM_CSEX,OleDbType.Boolean),
                                                new OleDbParameter(PARM_CFACE,OleDbType.Integer),
                                                new OleDbParameter(PARM_IDCARD,OleDbType.VarChar,32),
                                                 new OleDbParameter(PARM_CBZ,OleDbType.VarChar,512),
                                                new OleDbParameter(PARM_CID,OleDbType.Integer)
                                             };
            contactsParms[0].Value = contacts.Gid;
            contactsParms[1].Value = contacts.Cname;
            contactsParms[2].Value = contacts.Cbirthday;
            contactsParms[3].Value = contacts.Csex;
            contactsParms[4].Value = contacts.Cface;
            contactsParms[5].Value = contacts.IdCard;
            contactsParms[6].Value = contacts.CBz; 
            contactsParms[7].Value = contacts.Cid;
            
            OleDbParameter[] telParms = { 
                                         new OleDbParameter("@phone",OleDbType.VarChar,16),
                                         new OleDbParameter("@phone1",OleDbType.VarChar,16),
                                         new OleDbParameter("@family",OleDbType.VarChar,16),
                                         new OleDbParameter("@company",OleDbType.VarChar,16),
                                         new OleDbParameter("@fax",OleDbType.VarChar,16),
                                         new OleDbParameter(PARM_CID,OleDbType.Integer)
                                     };
            telParms[0].Value = tel.Phone;
            telParms[1].Value = tel.Phone1;
            telParms[2].Value = tel.Family;
            telParms[3].Value = tel.Company;
            telParms[4].Value = tel.Fax;
            telParms[5].Value = tel.Cid;

            OleDbParameter[] addrParms = { 
                                         new OleDbParameter("@family",OleDbType.VarChar,256),
                                         new OleDbParameter("@company",OleDbType.VarChar,256),
                                         new OleDbParameter("@other",OleDbType.VarChar,256),
                                         new OleDbParameter(PARM_CID,OleDbType.Integer)
                                     };
            addrParms[0].Value = addr.Family;
            addrParms[1].Value = addr.Company;
            addrParms[2].Value = addr.Other;
            addrParms[3].Value = addr.Cid;

            OleDbParameter[] networkParms = { 
                                         new OleDbParameter("@email",OleDbType.VarChar,128),
                                         new OleDbParameter("@qq",OleDbType.VarChar,128),
                                         new OleDbParameter("@msn",OleDbType.VarChar,128),
                                         new OleDbParameter("@blog",OleDbType.VarChar,128),
                                         new OleDbParameter("@website",OleDbType.VarChar,128),
                                           new OleDbParameter(PARM_CID,OleDbType.Integer)
                                     };
            networkParms[0].Value = network.Email;
            networkParms[1].Value = network.QQ;
            networkParms[2].Value = network.Msn;
            networkParms[3].Value = network.Blog;
            networkParms[4].Value = network.WebSite;
            networkParms[5].Value = network.Cid;
            int resoult = 0;
            OleDbConnection con = new OleDbConnection(AccessHelper.ConnectionString);
            con.Open();
            OleDbTransaction tra = con.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                AccessHelper.ExecuteNonQuery(tra, CommandType.Text, SQL_UPDATE_CONTACTS, contactsParms);
                AccessHelper.ExecuteNonQuery(tra, CommandType.Text, SQL_UPDATE_TEL, telParms);
                AccessHelper.ExecuteNonQuery(tra, CommandType.Text, SQL_UPDATE_ADDRESS, addrParms);
                AccessHelper.ExecuteNonQuery(tra, CommandType.Text, SQL_UPDATE_NETWORK, networkParms);
                tra.Commit();
                resoult = 1;
            }
            catch (Exception ex)
            {
                resoult = 0;
                tra.Rollback();
            }
            finally
            {
                con.Close();
            }
            return resoult;
        }
        //查询最后一个联系人编码
        public int GetMaxCid()
        {
            int cid = 0;
            cid = (int)AccessHelper.ExecuteScalar(AccessHelper.ConnectionString, CommandType.Text, SQL_SELECT_MAX_BY_CID, null);
            return cid;
        }
        //编辑刚刚
        #endregion
    }
}
